﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows;


namespace MyWPFDB
{
    /// <summary>
    /// Логика взаимодействия для MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        #region Логика вкладки Запрос


        private void Button1Click(object sender, RoutedEventArgs e)
        {
            try
            {
                resultTB.Text = ReadData(connStr.Text, queryTB.Text);
            }
            catch (Exception ex)
            {
                resultTB.Text = string.Format("Error: {0}", ex.Message);
            }
        }

        private static string ReadData(string connectionString, string queryString)
        {
            var strBuilder = new StringBuilder();
            using (var connection =
                  new SqlConnection(connectionString))
            {
                var command = new SqlCommand(queryString, connection);
                connection.Open();

//               SqlDataAdapter da = new SqlDataAdapter(command);
//               DataTable dtTable = new DataTable();
//               da.Fill(dtTable);


                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        strBuilder.AppendFormat("{0} - {1};", reader.GetName(i), reader[i]);
                    }
                    strBuilder.AppendLine();
                }
                reader.Close();

               connection.Close();
            }
            return strBuilder.ToString();
        }


        #endregion


        #region Логика вкладки Редактирование

        public void ShowData()
        {
           string databseConnectionString = ConfigurationManager.AppSettings["DatabaseConnectionString"];
//           ConfigurationManager.ConnectionStrings["cfdsfs"].ConnectionString


            SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=Contacts;Integrated Security=True;MultipleActiveResultSets=True");

            con.Open();

            SqlCommand comm = new SqlCommand("Select * from tablePers", con);

            DataTable dt = new DataTable();

            SqlDataAdapter da = new SqlDataAdapter(comm);

            da.Fill(dt);

            listView1.DataContext = dt.DefaultView;

//            PersonData data = new PersonData();
//            listView1.DataContext = data.LoadAll();

//           int aint = 5; // ToStirng() -> "5" 
//           DataTable adt = new DataTable(); // ToString() -> "System.Data.DataTable"
        }


        //Вызовим эту функцию в событии Window_Loaded()
        

        #endregion


        #region События кнопок

        private void BtnAddClick(object sender, RoutedEventArgs e)
        {
            string Name = textBox1.Text;
            string Surename = textBox2.Text;
            string Birthday = textBox3.Text;
            string Phone = textBox4.Text;
            string Email = textBox5.Text;
            SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=Contacts;Integrated Security=True;");
            con.Open();
            SqlCommand comm = new SqlCommand("insert into tablePers(Name,Surname,Birthday,Phone,Email) values(@Name,@Surname,@Birthday,@Phone,@Email)", con);
            comm.Parameters.AddWithValue("@Name", textBox1.Text);
            comm.Parameters.AddWithValue("@Surname", textBox2.Text);
            comm.Parameters.AddWithValue("@Birthday", textBox3.Text);
            comm.Parameters.AddWithValue("@Phone", textBox4.Text);
            comm.Parameters.AddWithValue("@Email", textBox5.Text);
            comm.ExecuteNonQuery();
            con.Close();
            ShowData();
        }



        private void BtnClearClick(object sender, RoutedEventArgs e)
        {
            textBox1.Text = "";
            textBox2.Text = "";
            textBox3.Text = "";
            textBox4.Text = "";
            textBox5.Text = "";
            ShowData();
        }
        

        private void BtnDeleteClick(object sender, RoutedEventArgs e)
        {
            if (listView1.SelectedItems.Count > 0)
            {
                DataRowView drv = (DataRowView)listView1.SelectedItem;
                string id = drv.Row[0].ToString();
                SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=Contacts;Integrated Security=True;");
                con.Open();
                SqlCommand comm = new SqlCommand("delete from tablePers where ContID=@ContID", con);
                comm.Parameters.AddWithValue("@ContID", id);
                comm.ExecuteNonQuery();
                ShowData();
            }
        }



        private void BtnUpdateClick(object sender, RoutedEventArgs e)
        {
            if (listView1.SelectedItems.Count > 0)
            {
                DataRowView drv = (DataRowView)listView1.SelectedItem;
                string id = drv.Row[0].ToString();
                SqlConnection con = new SqlConnection(@"Data Source=.;Initial Catalog=Contacts;Integrated Security=True;");
                con.Open();
                SqlCommand comm = new SqlCommand("update tablePers set Name=@Name,Surname=@Surname,Birthday=@Birthday,Phone=@Phone,Email=@Email where ContID=@ContID", con);

                comm.Parameters.AddWithValue("@ContID", id);
                comm.Parameters.AddWithValue("@Name", textBox1.Text);
                comm.Parameters.AddWithValue("@Surname", textBox2.Text);
                comm.Parameters.AddWithValue("@Birthday", textBox3.Text);
                comm.Parameters.AddWithValue("@Phone", textBox4.Text);
                comm.Parameters.AddWithValue("@Email", textBox5.Text);
                comm.ExecuteNonQuery();
                con.Close();
                ShowData();
            }
        }

        #endregion



    }
}
